﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public static class PrivacySettingDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static PrivacySetting SetPrivacySetting(PrivacySetting privacySetting)
        {
            if (privacySetting != null)
            {
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(ConnString);
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [PRIVACYSETTING](UserId, Setting)" +
                                       " VALUES(@userId, @setting)";
                    cmd.Parameters.AddWithValue("userId", privacySetting.UserId);
                    cmd.Parameters.AddWithValue("setting", privacySetting.Setting);
                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting a privacy setting");
                    }
                    privacySetting.PrivacyId = Convert.ToInt64(cmdId.ExecuteScalar(), CultureInfo.CurrentCulture);
                    return privacySetting;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("An error has ocurred while setting a privacy setting", ex);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static PrivacySetting GetPrivacySettingByUserId(long userId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PRIVACYSETTING] Where UserId = @userId";
                cmd.Parameters.AddWithValue("userId", userId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                PrivacySetting privSetting = new PrivacySetting();

                while (reader.Read())
                {
                    privSetting.PrivacyId = Int64.Parse(reader["PrivacyId"].ToString(), CultureInfo.CurrentCulture);
                    privSetting.UserId = Int64.Parse(reader["UserId"].ToString(), CultureInfo.CurrentCulture);
                    privSetting.Setting = reader["Setting"].ToString();
                }
                return privSetting;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting an user's privacy setting", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static PrivacySetting EditPrivacySetting(string setting, long userId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "UPDATE [dbo].[PRIVACYSETTING] SET Setting = @setting WHERE UserId=@userId ";
                cmd.Parameters.AddWithValue("setting", setting);
                cmd.Parameters.AddWithValue("userId", userId);
                conn.Open();
                int result = cmd.ExecuteNonQuery();
                if (result <= 0)
                {
                    return null;
                }
                return new PrivacySetting { UserId=userId, Setting=setting };
            }
            catch (SqlException)
            {
                throw new DataAccessException("An error has ocurred while editing a privacy setting");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
